﻿CREATE PROCEDURE [dbo].[utl_IsJobRunning]
@JobName [sysname], @IsRunning VARCHAR (1) OUTPUT
AS
BEGIN
SELECT 1
/*
	SET NOCOUNT ON
	
	DECLARE @job_id            UNIQUEIDENTIFIER
	DECLARE @is_sysadmin       INT
	DECLARE @job_owner         sysname
	DECLARE @execution_status  INT
	
	-- initialize variables 
	SET @IsRunning = 'N'
	SET @job_id = NULL
	SET @is_sysadmin = 0
	SET @job_owner = NULL
	SET @execution_status = 0
	
	-- set job id
	SET @job_id = (
	        SELECT job_id
	        FROM   msdb..sysjobs
	        WHERE  NAME = @JobName
	    )
	
	IF (@job_id IS NULL)
	BEGIN
	    RAISERROR('Unknown job name', 16, 1)
	    RETURN 1
	END
	
	-- create temp table
	CREATE TABLE #xp_sqlagent_enum_jobs_results
	(
		job_id                 UNIQUEIDENTIFIER NOT NULL,
		last_run_date          INT NOT NULL,
		last_run_time          INT NOT NULL,
		next_run_date          INT NOT NULL,
		next_run_time          INT NOT NULL,
		next_run_schedule_id   INT NOT NULL,
		requested_to_run       INT NOT
		NULL,
		request_source         INT NOT NULL,
		request_source_id      sysname NULL,
		running                INT NOT
		NULL,
		current_step           INT NOT NULL,
		current_retry_attempt  INT NOT NULL,
		job_state              INT NOT NULL
	)
	
	-- set sysadmin flag
	SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0); 
	SET @is_sysadmin = 1
	
	-- set job owner
	SELECT @job_owner = SUSER_SNAME()
	
	-- populate #xp_results
	INSERT INTO #xp_sqlagent_enum_jobs_results
	EXECUTE MASTER.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
	
	-- set execution status
	SET @execution_status = (
	        SELECT job_state
	        FROM   #xp_sqlagent_enum_jobs_results
	        WHERE  job_id = @job_id
	    )
	
	-- set is running flag
	IF (@execution_status = 1)
	    SET @IsRunning = 'Y'
	
	-- drop #xp_sqlagent_enum_jobs_results
	IF EXISTS (
	       SELECT 1
	       WHERE  (
	                  OBJECT_ID('tempdb..#xp_sqlagent_enum_jobs_results') IS NOT NULL
	              )
	   )
	    DROP TABLE #xp_sqlagent_enum_jobs_results
	
	SET NOCOUNT OFF
	
	RETURN 0
*/
END

